package hc.util

import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;

@groovy.transform.CompileStatic
class ExcelReader {
	static class ExcelReaderOptions {
		String fileName
		ExcelReaderOptions withFile(String fileName) {
			this.fileName = fileName
			return this
		}

		String anchorAt
		ExcelReaderOptions withAnchorAt(String anchorAt) {
			this.anchorAt = anchorAt
			return this
		}

		int tableWidthFixed
		ExcelReaderOptions withTableWidthFixed(int tableWidthFixed) {
			this.tableWidthFixed = tableWidthFixed
			return this
		}

		int tableWidthReferToRow = -1
		ExcelReaderOptions withTableWidthReferToRow(int tableWidthReferToRow) {
			this.tableWidthReferToRow = tableWidthReferToRow
			return this
		}

		int tableHeightReferToColumn =-1
		ExcelReaderOptions withTableHeightReferToColumn(int tableHeightReferToColumn) {
			this.tableHeightReferToColumn = tableHeightReferToColumn
			return this
		}

		ExcelReaderOptions withTableHeightReferToFirstColumn() {
			withTableHeightReferToColumn(0)
		}

		boolean nullCellFilledBlank
		ExcelReaderOptions enableNullCellFilledBlank() {
			this.nullCellFilledBlank = true
			return this
		}

		boolean trimCellText
		ExcelReaderOptions enableTrimCellText() {
			this.trimCellText = true
			return this
		}

		boolean optionsDone
		ExcelReader build() {
			this.optionsDone = true
			new ExcelReader(this)
		}
	}

	static class MergedRegion {
		int firstRow, lastRow,firstColumn,lastColumn
		String text

		boolean cover(int row , int col) {
			firstRow <= row && row <=lastRow &&
					firstColumn <= col && col <= lastColumn
		}
	}

	static ExcelReaderOptions options() {
		new ExcelReaderOptions()
	}

	ExcelReaderOptions options

	ExcelReader(ExcelReaderOptions options) {
		this.options = options
	}

	XSSFWorkbook workbook
	XSSFSheet sheet
	private int totalRow //max index rather then count

	private openSheet0() {
		XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream(this.options.fileName));
		sheet = workbook.getSheetAt(0)
		totalRow = sheet.physicalNumberOfRows
	}

	private int startRow
	private int startCol
	private markTableBorder()
	{
		if(options.anchorAt) {
			for(int ri =0; ri<=totalRow;ri++)
			{
				XSSFRow row = sheet.getRow(ri)

				if(row == null )continue;

				int totalCol = row.physicalNumberOfCells

				for(int ci = 0; ci<=totalCol; ci ++)
				{
					XSSFCell cell = row.getCell(ci)

					String cellText = getCellText(cell)

					if(options.anchorAt == cellText
					|| options.anchorAt == getCellComment(cell))
					{

						startRow = ri;
						startCol = ci;
						break;
					}

				}
			}
		}

		//initialize table width
		if(options.tableWidthFixed > 0 )
		{
			tableColumnBorder = startCol + options.tableWidthFixed-1;

		} else if(options.tableWidthReferToRow >= 0 )
		{
			def ri =startRow + options.tableWidthReferToRow;
			XSSFRow row = sheet.getRow(ri);
			def ci = row.physicalNumberOfCells

			while("" == getMergedCellText(row.getCell(ci), ri, ci)) ci--;

			tableColumnBorder = ci;
		}

	}

	int tableColumnBorder

	private String getCellText(XSSFCell cell)
	{
		if(cell) {
			if(cell.getCellType() != CellType.STRING)
				cell.setCellType(CellType.STRING)

			String txt = cell.getStringCellValue()

			if(options.trimCellText) txt = txt.trim();

			return txt
		}

		return ""
	}

	private String getCellComment(XSSFCell cell)
	{
		if(cell) {

			String txt =cell.getCellComment()?.getString()?.toString()

			if(options.trimCellText) txt = txt?.trim();

			return txt
		}

		return ""
	}

	ArrayList<MergedRegion> mergedRegionList = new ArrayList<MergedRegion>()

	def fillMergedCellList(){
		int sheetmergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetmergeCount; i++) {

			CellRangeAddress range = sheet.getMergedRegion(i);

			def region = new MergedRegion();

			region.firstColumn = range.getFirstColumn();
			region.lastColumn = range.getLastColumn();
			region.firstRow = range.getFirstRow();
			region.lastRow = range.getLastRow();

			region.text = getCellText (sheet.getRow(region.firstRow).getCell(region.firstColumn))

			mergedRegionList.add(region)
		}
	}

	String getMergedCellText(XSSFCell cell,int row, int col) {
		MergedRegion finded =
				mergedRegionList.find {
					it.cover(row,col)
				}

		if(finded) return finded.text;

		getCellText(cell);
	}

	ArrayList<ArrayList<String>> readTable() {

		openSheet0();

		fillMergedCellList();

		markTableBorder();

		def table = new ArrayList<ArrayList<String>>();

		for(int ri = startRow ; ri <= totalRow; ri ++)
		{
			XSSFRow row = sheet.getRow(ri)
			if(row == null) continue;

			int colCount = row.physicalNumberOfCells

			if(colCount > this.tableColumnBorder) colCount = this.tableColumnBorder

			ArrayList trow = new ArrayList<String>();

			for(int ci = startCol;ci <= colCount;ci++) {

				def cellText = getMergedCellText(row.getCell(ci), ri, ci);

				trow.add(cellText);
			}

			if(options.nullCellFilledBlank && colCount < tableColumnBorder)
				for(int ci = colCount + 1; ci <= tableColumnBorder; ci ++ )
			{
				trow.add("");
			}

			//Only return the row whose first column is not empty
			//if (trow.size()>0 && trow[0]!=null && trow[0] != "")

			table.add(trow)

		}

		return table;
	}

	public static void main(String[] args) {

		def fileName = 'src/hc/bre/dtable/decisiontableSimpleSample.xlsx'

		Object table =ExcelReader.options()
				.withFile(fileName)
				.withAnchorAt('决策表名称')
				.withTableWidthReferToRow(1)
				.enableNullCellFilledBlank()
				.build()
				.readTable();


		println table
	}

}
